package com.hrhx.duhongming.web;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.RowSetDynaClass;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.hrhx.duhongming.config.ConstantExcelPath;
import com.hrhx.duhongming.util.PoiToHtmlUtil;

import net.sf.jxls.report.ReportManager;
import net.sf.jxls.report.ReportManagerImpl;
import net.sf.jxls.report.ResultSetCollection;
import net.sf.jxls.sample.dynamicColumns.Columns;
import net.sf.jxls.sample.dynamicColumns.FundsVO;
import net.sf.jxls.sample.dynamicColumns.ValueBean;
import net.sf.jxls.sample.model.Department;
import net.sf.jxls.sample.model.Employee;
import net.sf.jxls.sample.util.DatabaseHelper;
import net.sf.jxls.sample.util.RedCellProcessor;
import net.sf.jxls.sample.util.StyleRowProcessor;
import net.sf.jxls.transformer.Configuration;
import net.sf.jxls.transformer.XLSTransformer;

@Controller
@RequestMapping(value = "/jxls1")
public class Jxls1Controller {
	
	@RequestMapping(value = "{templates}/{suffix}", method = RequestMethod.GET)
	public String templates(@PathVariable("templates") String templates,
							@PathVariable("suffix") String suffix,
							ModelMap modelMap) {
		String templateFileName = ConstantExcelPath.getExcelTemplatePath()+templates+"."+suffix;
		String destFileName = ConstantExcelPath.getExcelTempPath()+templates+"."+suffix;
		
		try {
			transformXLS(templates,templateFileName,destFileName);
			String excelHtml = PoiToHtmlUtil.excel2003ToHtml(destFileName);
			modelMap.put("excelHtml",excelHtml);
			modelMap.put("templates",templates);
			modelMap.put("suffix",suffix);
		} catch (Exception e) {
		}
		return "/jxls1/demo";
	}
	
	private void transformXLS(String templates,String templateFileName,String destFileName){
		try {
			XLSTransformer transformer = new XLSTransformer();
			Map<String,Object> map = null;
			if("adjacentlists".equals(templates)){
				map = getAdjacentlistsData();
			}else if("basictags".equals(templates)){
				map = getBasictagsData();
			}else if("chart".equals(templates)){
				map = getChartData();
				transformer.markAsFixedSizeCollection("employee");
			}else if("colouring".equals(templates)){
				map = getColouringData();
				transformer.registerCellProcessor(new RedCellProcessor("department.staff"));
			}else if("grouping".equals(templates)){
				map = getGroupingData();
				transformer.groupCollection( "departments0.staff.name" );
			}else if("hiddencolumn".equals(templates)){
				map = getHiddencolumnData();
				transformer.setColumnPropertyNamesToHide(new String[]{"age"});
		        //transformer.setColumnsToHide(new short[]{1});
		        transformer.groupCollection("department.staff");
			}else if("multiplelistrows".equals(templates)){
				map = getMultiplelistrowsData();
				//transformer.registerCellProcessor( new RedCellProcessor("department.staff") );
		        //transformer.registerCellProcessor( new MultPropertyCellProcessor("department.staff", adjProperties ));
		        //transformer.registerCellProcessor( new MultPropertyCellProcessor("department.chief", adjProperties));
		        //transformer.groupCollection("department.staff");
			}else if("report".equals(templates)){
				map = getReportData();
			}else if("resultset".equals(templates)){
				map = getResultsetData();
			}else if("rowset".equals(templates)){
				map = getRowsetData();
			}else if("simple".equals(templates)){
				map = getSimpleData();
				transformer.groupCollection("department.staff");
			}else if("singlelist".equals(templates)){
				map = getSinglelistData();
				Configuration config = new Configuration();
				config.setUTF16( true );
				transformer.setConfiguration(config);
		        transformer.groupCollection( "employee.name");
			}else if("rowstyle".equals(templates)){
				map = getRowstyleData();
				transformer.registerRowProcessor(new StyleRowProcessor("department.staff"));
			}else if("ex_temp".equals(templates)){
				map = getExtempData();
			}
			transformer.transformXLS(templateFileName, map, destFileName);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private Map<String, Object> getExtempData() {
		List<ValueBean> contractList = new ArrayList<ValueBean>();
		
		String afvdat_brk ="";	
		String dlnrnr = "";		
		String echtsnr ="";
		String gebdat_brk = "";
		String gesl_brk ="";
		String nm_dlnr_brk = "";
		String pakketnr = "";
		String rekeningnummer = "";
		String srt_brk_brk = "";
		String tarlgnr = "";		
		String verznr = "";
		
		for (int i = 0; i < 10; i++) {
			afvdat_brk =i+"1";	
			dlnrnr = i+"2";		
			echtsnr = i+"3";
			gebdat_brk = i+"4";
			gesl_brk = i+"5";
			nm_dlnr_brk = i+"6";
			pakketnr = i+"7";
			rekeningnummer = i+"8";
			srt_brk_brk = i+"9";
			tarlgnr = i+"10";		
			verznr = i+"11";
			
			ValueBean con=new ValueBean();
			
			con.setDlnrnr(dlnrnr);
			con.setAfvdat_brk(afvdat_brk);
			con.setEchtsnr(echtsnr);
			con.setGebdat_brk(gebdat_brk);
			con.setGesl_brk(gesl_brk);
			con.setNm_dlnr_brk(nm_dlnr_brk);
			con.setPakketnr(pakketnr);
			con.setRekeningnummer(rekeningnummer);
			con.setSrt_brk_brk(srt_brk_brk);
			con.setTarlgnr(tarlgnr);
			con.setVerznr(verznr);
			ArrayList<FundsVO> fundsVOs = new ArrayList<FundsVO>();
			for (int j = 0; j < 10; j++) {
				FundsVO fundVO = new FundsVO();
				fundVO.setEenheden( ""+i+j+"12");
				fundVO.setWaarde(""+i+j+"13");
				fundVO.setVerkoopkosten(""+i+j+"14");
				fundsVOs.add(fundVO); 
			}
			con.setFund(fundsVOs);			
			contractList.add(con);
		}

		ArrayList<ValueBean> header = new ArrayList<ValueBean>();	
		header.add(new ValueBean("88888", "18/01/1987", "01/02/1956","26/08/1954"));

		ArrayList<Columns> cols = new ArrayList<Columns>();	
		for (int j = 0; j < 10; j++) {
			cols.add(new Columns("001"+j,"18"+j+"€"));
		}
	
		Map<String, Object> beans = new HashMap<>();
		beans.put("records", contractList);
		beans.put("header", header);
		beans.put("cols", cols);
		return beans;
	}

	private Map<String, Object> getRowstyleData() {
		Department department = new Department("IT");
        Calendar calendar = Calendar.getInstance();
        calendar.set(1970, 12, 2);
        Date d1 = calendar.getTime();
        calendar.set(1980, 2, 15);
        Date d2 = calendar.getTime();
        calendar.set(1976, 7, 20);
        Date d3 = calendar.getTime();
        calendar.set(1968, 5, 6);
        Date d4 = calendar.getTime();
        calendar.set(1978, 8, 17);
        Date d5 = calendar.getTime();
        department.addEmployee(new Employee("Elsa", 28, 1500, 0.15, d1));
        department.addEmployee(new Employee("Oleg", 32, 2300, 0.25, d3));
        department.addEmployee(new Employee("Neil", 34, 2500, 0.00, d4));
        department.addEmployee(new Employee("Maria", 34, 1700, 0.15, d5));
        department.addEmployee(new Employee("John", 35, 2800, 0.20, d2));
        Map<String,Object> beans = new HashMap<>();
        beans.put("department", department);
        return beans;
	}

	private Map<String, Object> getSinglelistData() {
		Department department = new Department("IT");
        Calendar calendar = Calendar.getInstance();
        calendar.set(1970, 12, 2);
        Date d1 = calendar.getTime();
        calendar.set(1980, 2, 15);
        Date d2 = calendar.getTime();
        calendar.set(1976, 7, 20);
        Date d3 = calendar.getTime();
        calendar.set(1968, 5, 6);
        Date d4 = calendar.getTime();
        calendar.set(1978, 8, 17);
        Date d5 = calendar.getTime();
        Employee chief = new Employee("Derek", 35, 3000, 0.30, d1);
        department.setChief(chief);
        Employee elsa = new Employee("Elsa", 28, 1500, 0.15, d2);
        department.addEmployee(elsa);
        Employee oleg = new Employee("Oleg", 32, 2300, 0.25, d3);
        department.addEmployee(oleg);
        Employee neil = new Employee("Neil", 34, 2500, 0.00, d4);
        department.addEmployee(neil);
        Employee maria = new Employee("Maria", 34, 1700, 0.15, d5);
        department.addEmployee(maria);
        Employee john = new Employee("John", 35, 2800, 0.20, d2);
        department.addEmployee(john);
        maria.setSuperior(oleg);
        oleg.setSuperior(john);
        neil.setSuperior(john);
        Map<String,Object> beans = new HashMap<>();
        beans.put("department", department);
        return beans;
	}

	private Map<String, Object> getSimpleData() {
		Collection<Employee> staff = new HashSet<Employee>();
        staff.add(new Employee("Derek", 35, 3000, 0.30));
        staff.add(new Employee("Elsa", 28, 1500, 0.15));
        staff.add(new Employee("Oleg", 32, 2300, 0.25));
        staff.add(new Employee("Neil", 34, 2500, 0.00));
        staff.add(new Employee("Maria", 34, 1700, 0.15));
        staff.add(new Employee("John", 35, 2800, 0.20));
        Map<String,Object> beans = new HashMap<>();
        beans.put("employee", staff);
        return beans;
	}

	private Map<String,Object> getRowsetData() throws Exception {
		DatabaseHelper dbHelper = new DatabaseHelper();
        Class.forName("org.hsqldb.jdbcDriver");
        Connection con = DriverManager.getConnection("jdbc:hsqldb:mem:jxls", "sa", "");
        dbHelper.initDatabase( con );
        // get result set
        Statement stmt = con.createStatement();
        String query = "SELECT name, age, payment, bonus, birthDate FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        // second parameter to RowSetDynaClass constructor indicates should the properties be lowercased
        RowSetDynaClass rsdc = new RowSetDynaClass(rs, true);
		Map<String,Object> beans = new HashMap<String,Object>();
        beans.put("employee", rsdc.getRows());
        stmt.close();
        rs.close();
        con.close();
        return beans;
	}
	private Map<String,Object> getResultsetData() throws Exception {
		DatabaseHelper dbHelper = new DatabaseHelper();
        Class.forName("org.hsqldb.jdbcDriver");
        Connection con = DriverManager.getConnection("jdbc:hsqldb:mem:jxls", "sa", "");
        dbHelper.initDatabase( con );
        // get result set
        Statement stmt = con.createStatement();
        // let's get number of records to be able to implement size() method in ResultSetCollection class
        String countQuery = "SELECT COUNT(*) FROM employee";
        ResultSet rs = stmt.executeQuery( countQuery );
        int count = 0;
        if( rs.next() ){
            count = rs.getInt( 1 );
        }

        String query = "SELECT name, age, payment, bonus, birthDate FROM employee";
        rs = stmt.executeQuery(query);
        Map<String,Object> beans = new HashMap<>();
        ResultSetCollection rsc = new ResultSetCollection(rs, count, true);
        beans.put("employee", rsc);
        stmt.close();
        rs.close();
        con.close();
        return beans;
	}
	private Map<String,Object> getReportData() throws Exception {
		DatabaseHelper dbHelper = new DatabaseHelper();
        Class.forName("org.hsqldb.jdbcDriver");
        Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:jxls", "sa", "");
        dbHelper.initDatabase( conn );
        Map<String,Object> beans = new HashMap<String,Object>();
        ReportManager reportManager = new ReportManagerImpl( conn, beans );
        beans.put("rm", reportManager);
        beans.put("minDate", "1979-01-01");
        return beans;
	}
	private Map<String,Object> getMultiplelistrowsData() {
		Department department = new Department("IT");
        Calendar calendar = Calendar.getInstance();
        calendar.set(1970, 12, 2);
        Date d1 = calendar.getTime();
        calendar.set(1980, 2, 15);
        Date d2 = calendar.getTime();
        calendar.set(1976, 7, 20);
        Date d3 = calendar.getTime();
        calendar.set(1968, 5, 6);
        Date d4 = calendar.getTime();
        calendar.set(1978, 8, 17);
        Date d5 = calendar.getTime();
        Employee chief = new Employee("Derek", 35, 3000, 0.30, d1);
        department.setChief(chief);
        Employee elsa = new Employee("Elsa", 28, 1500, 0.15, d2);
        department.addEmployee(elsa);
        Employee oleg = new Employee("Oleg", 32, 2300, 0.25, d3);
        department.addEmployee(oleg);
        Employee neil = new Employee("Neil", 34, 2500, 0.00, d4);
        department.addEmployee(neil);
        Employee maria = new Employee("Maria", 34, 1700, 0.15, d5);
        department.addEmployee(maria);
        Employee john = new Employee("John", 35, 2800, 0.20, d2);
        department.addEmployee(john);
        maria.setSuperior(oleg);
        oleg.setSuperior(john);
        neil.setSuperior(john);
        Map<String,Object> beans = new HashMap<>();
        beans.put("department", department);
        return beans;
	}
	private Map<String,Object> getHiddencolumnData() {
		Department department = new Department("IT");
        Calendar calendar = Calendar.getInstance();
        calendar.set(1970, 12, 2);
        Date d1 = calendar.getTime();
        calendar.set(1980, 2, 15);
        Date d2 = calendar.getTime();
        calendar.set(1976, 7, 20);
        Date d3 = calendar.getTime();
        calendar.set(1968, 5, 6);
        Date d4 = calendar.getTime();
        calendar.set(1978, 8, 17);
        Date d5 = calendar.getTime();
        Employee chief = new Employee("Derek", 35, 3000, 0.30, d1);
        department.setChief(chief);
        department.addEmployee(new Employee("Elsa", 28, 1500, 0.15, d2));
        department.addEmployee(new Employee("Oleg", 32, 2300, 0.25, d3));
        department.addEmployee(new Employee("Neil", 34, 2500, 0.00, d4));
        department.addEmployee(new Employee("Maria", 34, 1700, 0.15, d5));
        department.addEmployee(new Employee("John", 35, 2800, 0.20, d2));
        Map<String,Object> beans = new HashMap<>();
        beans.put("department", department);
        return beans;
	}
	private Map<String,Object> getGroupingData() {
		List<Department> departments = new ArrayList<Department>();
        Department department = new Department("IT");
        Employee chief = new Employee("Derek", 35, 3000, 0.30);
        department.setChief(chief);
        department.addEmployee(new Employee("Elsa", 28, 1500, 0.15));
        department.addEmployee(new Employee("Oleg", 32, 2300, 0.25));
        department.addEmployee(new Employee("Neil", 34, 2500, 0.00));
        department.addEmployee(new Employee("Maria", 34, 1700, 0.15));
        department.addEmployee(new Employee("John", 35, 2800, 0.20));
        departments.add(department);
        department = new Department("HR");
        chief = new Employee("Betsy", 37, 2200, 0.30);
        department.setChief(chief);
        department.addEmployee(new Employee("Olga", 26, 1400, 0.20));
        department.addEmployee(new Employee("Helen", 30, 2100, 0.10));
        department.addEmployee(new Employee("Keith", 24, 1800, 0.15));
        department.addEmployee(new Employee("Cat", 34, 1900, 0.15));
        departments.add(department);
        department = new Department("BA");
        chief = new Employee("Wendy", 35, 2900, 0.35);
        department.setChief(chief);
        department.addEmployee(new Employee("Denise", 30, 2400, 0.20));
        department.addEmployee(new Employee("LeAnn", 32, 2200, 0.15));
        department.addEmployee(new Employee("Natali", 28, 2600, 0.10));
        department.addEmployee(new Employee("Martha", 33, 2150, 0.25));
        departments.add(department);
        Map<String,Object> beans = new HashMap<>();
        beans.put("departments", departments);
        return beans;
	}
	private Map<String,Object> getColouringData() {
		Department department = new Department("IT");
        Calendar calendar = Calendar.getInstance();
        calendar.set(1970, 12, 2);
        Date d1 = calendar.getTime();
        calendar.set(1980, 2, 15);
        Date d2 = calendar.getTime();
        calendar.set(1976, 7, 20);
        Date d3 = calendar.getTime();
        calendar.set(1968, 5, 6);
        Date d4 = calendar.getTime();
        calendar.set(1978, 8, 17);
        Date d5 = calendar.getTime();
        Employee chief = new Employee("Derek", 35, 3000, 0.30, d1);
        department.setChief(chief);
        department.addEmployee(new Employee("Elsa", 28, 1500, 0.15, d2));
        department.addEmployee(new Employee("Oleg", 32, 2300, 0.25, d3));
        department.addEmployee(new Employee("Neil", 34, 2500, 0.00, d4));
        department.addEmployee(new Employee("Maria", 34, 1700, 0.15, d5));
        department.addEmployee(new Employee("John", 35, 2800, 0.20, d2));
        Map<String,Object> beans = new HashMap<>();
        beans.put("department", department);
        return beans;
	}
	private Map<String,Object> getChartData() {
		List<Employee> staff = new ArrayList<Employee>();
        staff.add(new Employee("Derek", 35, 3000, 0.30));
        staff.add(new Employee("Elsa", 28, 1500, 0.15));
        staff.add(new Employee("Oleg", 32, 2300, 0.25));
        staff.add(new Employee("Neil", 34, 2500, 0.00));
        staff.add(new Employee("Maria", 34, 1700, 0.15));
        staff.add(new Employee("John", 35, 2800, 0.20));
        staff.add(new Employee("Leonid", 29, 1700, 0.20));
        Map<String,Object> beans = new HashMap<>();
        beans.put("employee", staff);
        return beans;
	}
	private Map<String,Object> getBasictagsData() {
		List<Department> departments = new ArrayList<Department>();
        Department department = new Department("IT");
        Employee chief = new Employee("Derek", 35, 3000, 0.30);
        department.setChief(chief);
        department.addEmployee(new Employee("Elsa", 28, 1500, 0.15));
        department.addEmployee(new Employee("Oleg", 32, 2300, 0.25));
        department.addEmployee(new Employee("Neil", 34, 2500, 0.00));
        department.addEmployee(new Employee("Maria", 34, 1700, 0.15));
        department.addEmployee(new Employee("John", 35, 2800, 0.20));
        departments.add(department);
        department = new Department("HR");
        chief = new Employee("Betsy", 37, 2200, 0.30);
        department.setChief(chief);
        department.addEmployee(new Employee("Olga", 26, 1400, 0.20));
        department.addEmployee(new Employee("Helen", 30, 2100, 0.10));
        department.addEmployee(new Employee("Keith", 24, 1800, 0.15));
        department.addEmployee(new Employee("Cat", 34, 1900, 0.15));
        departments.add(department);
        department = new Department("BA");
        chief = new Employee("Wendy", 35, 2900, 0.35);
        department.setChief(chief);
        department.addEmployee(new Employee("Denise", 30, 2400, 0.20));
        department.addEmployee(new Employee("LeAnn", 32, 2200, 0.15));
        department.addEmployee(new Employee("Natali", 28, 2600, 0.10));
        department.addEmployee(new Employee("Martha", 33, 2150, 0.25));
        departments.add(department);
        Map<String,Object> beans = new HashMap<>();
        beans.put("departments", departments);
        return beans;
	}
	
	private Map<String,Object> getAdjacentlistsData(){
		Department depIT = new Department("IT");
        Employee chief = new Employee("Derek", 35, 3000, 0.30);
        depIT.setChief(chief);
        Employee elsa = new Employee("Elsa", 28, 1500, 0.15);
        depIT.addEmployee(elsa);
        Employee oleg = new Employee("Oleg", 32, 2300, 0.25);
        depIT.addEmployee(oleg);
        Employee neil = new Employee("Neil", 34, 2500, 0.00);
        depIT.addEmployee(neil);
        Employee maria = new Employee("Maria", 34, 1700, 0.15);
        depIT.addEmployee(maria);
        Employee john = new Employee("John", 35, 2800, 0.20);
        depIT.addEmployee(john);
        Department depHR = new Department("HR");
        Employee natali = new Employee("Natali", 25, 1200, 0.1);
        depHR.addEmployee( natali );
        Employee helen = new Employee("Helen", 27, 1100, 0.20);
        depHR.addEmployee(helen);
        Employee olga = new Employee("Olga", 24, 1150, 0.00);
        depHR.addEmployee(olga);
        Map<String,Object> beans = new HashMap<>();
        beans.put("depIT", depIT);
        beans.put("depHR", depHR);
        return beans;
	}

}
